-- Given a HierarchyKey returns the full path to the document starting with the root name.  The path is in
-- slash notation.  Like $/Common/Business Objects/DocumentType
CREATE FUNCTION [dbo].[asi_DocumentPathFromHierarchyKey](@hierarchyKey uniqueidentifier)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE
   @rootHierarchyKey uniqueidentifier,
   @documentFound bit,
   @continue int,
   @path nvarchar(4000)

   SET @path = ''
   SET @documentFound = 0
   SET @continue = 1

   SELECT @rootHierarchyKey = RootHierarchyKey
     FROM Hierarchy
    WHERE HierarchyKey = @hierarchyKey

   WHILE @hierarchyKey IS NOT NULL AND @hierarchyKey <> @rootHierarchyKey AND @continue > 0
   BEGIN
      SELECT TOP 1
             @path = DocumentMain.DocumentName + CASE WHEN LEN(@path) > 0 THEN '/' + @path ELSE '' END,
             @hierarchyKey = Hierarchy.ParentHierarchyKey,
             @rootHierarchyKey = Hierarchy.RootHierarchyKey
        FROM Hierarchy INNER JOIN DocumentMain on Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
       WHERE Hierarchy.HierarchyKey = @hierarchyKey
         AND (DocumentMain.DocumentStatusCode IN (40,50))
       ORDER BY DocumentMain.CreatedOn DESC

      SET @continue = @@ROWCOUNT

      IF @documentFound = 0  
      BEGIN
         SET @documentFound = 1
         SET @path = ''
      END
   END

   SELECT @path = HierarchyRootName + CASE WHEN LEN(@path) > 0 THEN '/' + @path ELSE '' END
     FROM HierarchyRoot
    WHERE RootHierarchyKey = @rootHierarchyKey

   RETURN @path
END

GO